package com.weaver.qfengx;

import com.google.common.base.Joiner;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.weaver.qfengx.annotation.DaoField;
import com.weaver.qfengx.config.DevModeConfig;
import com.weaver.qfengx.entity.PageInfo;
import com.weaver.qfengx.entity.PageQuery;
import weaver.conn.BatchRecordSet;
import weaver.conn.RecordSet;
import weaver.conn.RecordSetDataSource;
import weaver.conn.RecordSetTrans;
import weaver.general.BaseBean;

import java.lang.reflect.Field;
import java.util.*;

/**
 * TODO 切换为大小写不敏感的Map
 * Created by YeShengtao on 2020/8/6 17:20
 */
public class DaoUtils {

    private static final LogUtils log = new LogUtils(DaoUtils.class);

    private static boolean caseinsensitive = true; // 默认大小写不敏感

    static {
        refreshConfig();
    }

    public static void refreshConfig() { // 刷新日志级别
        String table = "uf_devConfig";
        RecordSet rs = new RecordSet();
        rs.executeQuery(String.format("select value from %s where name = ?", table), "daoutils.map.caseinsensitive");
        if (rs.next()) {
            String val = rs.getString("value");
            if (!StringUtils.isNullOrEmpty(val)) {
                // 0 关闭， 1 开启 默认开启
                if (NumberUtils.parseInt(val, 1) == 1) caseinsensitive = true;
                else caseinsensitive = false;
            }
        }
    }

    /**
     * 获取数据库类型
     *
     * @return dbType
     */
    public static String dbType() {
        RecordSet recordSet = new RecordSet();
        return recordSet.getDBType();
    }

    /**
     * 执行插入语句，map的键值作为插入的字段与字段值
     *
     * @param tableName
     * @param params
     */
    public static void insert(String tableName, Map<String, Object> params) {
        if (StringUtils.isEmpty(tableName)) {
            log.debugWriteLog("执行插入语句 => 表名为空");
            return;
        }
        if (Objects.isNull(params) || params.isEmpty()) {
            log.debugWriteLog("执行插入语句 => map为空");
            return;
        }
        List<String> paramList = Lists.newArrayList();
        List<Object> valueList = Lists.newArrayList();
        params.forEach((key, value) -> {
            paramList.add(key);
            valueList.add(value);
        });
        String sql = createInsertSql(tableName, paramList);
        log.debugWriteLog("执行SQL => " + sql);
        log.debugWriteLog("执行参数 => " + Arrays.toString(valueList.toArray()));
        executeUpdate(sql, valueList);
    }

    public static void insert(String tableName, List<String> params, List<Object> data) {
        if (StringUtils.isEmpty(tableName)) {
            log.debugWriteLog("执行插入语句 => 表名为空");
            return;
        }
        if (Objects.isNull(params) || params.isEmpty() || Objects.isNull(data) || data.isEmpty()) {
            log.debugWriteLog("执行插入语句 => 字段集合为空或数据集合为空");
            return;
        }
        String sql = createInsertSql(tableName, params);
        log.debugWriteLog("执行SQL => " + sql);
        log.debugWriteLog("执行参数 => " + Arrays.toString(data.toArray()));
        executeUpdate(sql, data);
    }

    public static void insertWithRst(RecordSetTrans rst, String tableName, List<String> params, List<Object> data) throws Exception {
        if (StringUtils.isEmpty(tableName)) {
            log.debugWriteLog("执行插入语句 => 表名为空");
            return;
        }
        if (Objects.isNull(params) || params.isEmpty() || Objects.isNull(data) || data.isEmpty()) {
            log.debugWriteLog("执行插入语句 => 字段集合为空或数据集合为空");
            return;
        }
        String sql = createInsertSql(tableName, params);
        log.debugWriteLog("执行SQL => " + sql);
        log.debugWriteLog("执行参数 => " + Arrays.toString(data.toArray()));
        rst.executeUpdate(sql, data);
    }

    public static void batchInsert(String tableName, List<String> params, List<List<Object>> data) {
        if (StringUtils.isEmpty(tableName)) {
            log.debugWriteLog("批量执行插入语句 => 表名为空");
            return;
        }
        if (Objects.isNull(params) || params.isEmpty() || Objects.isNull(data) || data.isEmpty()) {
            log.debugWriteLog("批量执行插入语句 => 字段集合为空或数据集合为空");
            return;
        }
        batchExecute(createInsertSql(tableName, params), data);
    }

    public static String createInsertSql(String tableName, List<String> params) {
        StringBuilder sql = new StringBuilder(String.format("insert into %s (", tableName));
        StringBuilder addParam = new StringBuilder(" values (");
        int i = 0;
        List<Object> paramList = Lists.newArrayList();
        for (String param : params) {
            if (i++ == 0) {
                sql.append(" " + param);
                addParam.append(" ?");
            } else {
                sql.append(", " + param);
                addParam.append(", ?");
            }
        }
        return sql.toString() + ")" + addParam.toString() + ")";
    }

    /**
     * 映射RecordSet到JavaBean
     *
     * @param recordSet
     * @param clazz
     * @param <T>
     * @return
     */
    public static <T> T recordSetMapToBean(RecordSet recordSet, Class<T> clazz) {
        try {
            T t = clazz.newInstance();
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                // 获取是否有注解
                DaoField daoField = field.getAnnotation(DaoField.class);
                if (Objects.nonNull(daoField)) {
                    if (!daoField.value()) { // 为false
                        continue;
                    }
                }
                field.setAccessible(true);
                String fieldName = field.getName();
                String value = recordSet.getString(fieldName);
                field.set(t, value);
            }
            return t;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
    }

    public static <T> T recordSetMapToBean(RecordSet recordSet, Class<T> clazz, Map<String, String> fieldMap) {
        try {
            T t = clazz.newInstance();
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                String fieldName = field.getName();
                String value = recordSet.getString(fieldMap.get(fieldName));
                field.setAccessible(true);
                field.set(t, value);
            }
            return t;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
    }

    /**
     * 查询表记录并封装到实体中
     *
     * @param tableName
     * @param clazz
     * @param <T>
     * @return
     */
    public static <T> List<T> queryTableToBean(String tableName, Class<T> clazz) {
        RecordSet recordSet = new RecordSet();
        String sql = String.format("select * from %s", tableName);
        List<T> tList = Lists.newArrayList();
        log.debugWriteLog("执行SQL => " + sql);
        recordSet.executeQuery(sql);
        while (recordSet.next())
            tList.add(recordSetMapToBean(recordSet, clazz));
        return tList;
    }

    public static <T> List<T> queryTableToBean(Class<T> clazz, String sql, Object... objs) {
        RecordSet recordSet = new RecordSet();
        List<T> tList = Lists.newArrayList();
        log.debugWriteLog("执行SQL => " + sql);
        recordSet.executeQuery(sql, objs);
        while (recordSet.next())
            tList.add(recordSetMapToBean(recordSet, clazz));
        return tList;
    }

    public static <T> List<T> queryTableToBean(String tableName, Class<T> clazz, Map<String, String> fields) {
//        BiMap<String, String> biMap = HashBiMap.create(fields);
//        biMap.inverse();
        RecordSet recordSet = new RecordSet();
        String sql = String.format("select * from %s", tableName);
        List<T> tList = Lists.newArrayList();
        log.debugWriteLog("执行SQL => " + sql);
        recordSet.executeQuery(sql);
        while (recordSet.next())
            tList.add(recordSetMapToBean(recordSet, clazz, fields));
        return tList;
    }

    public static <T> List<T> querySqlToBean(String sql, Class<T> clazz, Map<String, String> fields) {
        RecordSet recordSet = new RecordSet();
        List<T> tList = Lists.newArrayList();
        log.debugWriteLog("执行SQL => " + sql);
        recordSet.executeQuery(sql);
        while (recordSet.next())
            tList.add(recordSetMapToBean(recordSet, clazz));
        return tList;
    }

    public static <T> T queryTableToSingleBean(String tableName, Class<T> clazz, Map<String, String> fields) {
        RecordSet recordSet = new RecordSet();
        String sql = String.format("select * from %s", tableName);
        log.debugWriteLog("执行SQL => " + sql);
        recordSet.executeQuery(sql);
        if (recordSet.next())
            return recordSetMapToBean(recordSet, clazz, fields);
        return null;
    }

    public static <T> T queryTableToSingleBean(String tableName, Class<T> clazz) {
        RecordSet recordSet = new RecordSet();
        String sql = String.format("select * from %s", tableName);
        log.debugWriteLog("执行SQL => " + sql);
        recordSet.executeQuery(sql);
        if (recordSet.next())
            return recordSetMapToBean(recordSet, clazz);
        return null;
    }

    public static String querySingleVal(String sql, Object... objs) {
        RecordSet recordSet = new RecordSet();
        log.debugWriteLog("执行SQL => " + sql);
        log.debugWriteLog("SQL参数 => " + Arrays.toString(objs));
        recordSet.executeQuery(sql, objs);
        if (recordSet.next()) {
            return recordSet.getString(1);
        }
        return null;
    }

    /**
     * 保存
     *
     * @param clazz     clazz
     * @param tableName tableName
     * @param list      list
     * @throws Exception Exception
     */
    public static void saveList(Class clazz, String tableName, List<?> list) throws Exception {
        List<String> fields = BUtils.beanAttrNameList(clazz);
        StringBuilder stringBuffer = new StringBuilder(String.format("insert into %s (", tableName));
        StringBuilder temp = new StringBuilder();
        for (int i = 0; i < fields.size(); i++) {
            if (i == 0) {
                stringBuffer.append(fields.get(i));
                temp.append("?");
            } else {
                stringBuffer.append(", " + fields.get(i));
                temp.append(", ?");
            }
        }
        List<List<Object>> params = Lists.newArrayList();
        for (Object obj : list) {
            params.add(BUtils.beanToAttrListWithSq(obj, fields));
        }
        stringBuffer.append(") values (" + temp.toString() + ")");
        log.debugWriteLog("执行SQL => " + stringBuffer.toString());
        batchExecute(stringBuffer.toString(), params);
    }

    /**
     * 保存
     *
     * @param fields    clazz
     * @param tableName tableName
     * @param list      list
     * @throws Exception Exception
     */
    public static void saveList(Map<String, String> fields, String tableName, List<?> list) throws Exception {
        StringBuffer stringBuffer = new StringBuffer(String.format("insert into %s (", tableName));
        List<String> keyList = Lists.newArrayList(fields.keySet());
        List<String> valueList = Lists.newArrayList();
        StringBuffer temp = new StringBuffer();
        for (int i = 0; i < keyList.size(); i++) {
            String value = fields.get(keyList.get(i));
            valueList.add(value);
            if (i == 0) {
                stringBuffer.append(value);
                temp.append("?");
            } else {
                stringBuffer.append(", " + value);
                temp.append(", ?");
            }
        }
        List<List<Object>> params = Lists.newArrayList();
        for (Object obj : list) {
            params.add(BUtils.beanToAttrListWithSq(obj, keyList));
        }
        stringBuffer.append(") values (" + temp.toString() + ")");
        log.debugWriteLog("执行SQL => " + stringBuffer.toString());
        batchExecute(stringBuffer.toString(), params);
    }

    /**
     * 查询SQL并封装到实体中
     *
     * @param clazz
     * @param sql
     * @param objs
     * @param <T>
     * @return
     */
    public static <T> List<T> querySqlToBean(String sql, Class<T> clazz, Object... objs) {
        RecordSet recordSet = new RecordSet();
        List<T> tList = Lists.newArrayList();
        log.debugWriteLog("执行SQL => " + sql);
        log.debugWriteLog("SQL参数 => " + Arrays.toString(objs));
        recordSet.executeQuery(sql, objs);
        while (recordSet.next())
            tList.add(recordSetMapToBean(recordSet, clazz));
        return tList;
    }

    public static <T> T findSqlToBean(Class<T> clazz, String sql, Object... objs) {
        RecordSet recordSet = new RecordSet();
        log.debugWriteLog("执行SQL => " + sql);
        log.debugWriteLog("SQL参数 => " + Arrays.toString(objs));
        recordSet.executeQuery(sql, objs);
        if (recordSet.next())
            return recordSetMapToBean(recordSet, clazz);
        return null;
    }

    public static RecordSet executeQuery(String sql, Object... objs) {
        RecordSet recordSet = new RecordSet();
        log.debugWriteLog("执行SQL => " + sql);
        log.debugWriteLog("SQL参数 => " + Arrays.toString(objs));
        if (recordSet.executeQuery(sql, objs))
            return recordSet;
        return null;
    }

    public static List<Map<String, String>> executeQueryToMapList(String sql, Object... objs) {
        RecordSet recordSet = new RecordSet();
        log.debugWriteLog("执行SQL => " + sql);
        log.debugWriteLog("SQL参数 => " + Arrays.toString(objs));
        List<Map<String, String>> res = Lists.newArrayList();
        if (recordSet.executeQuery(sql, objs)) {
            while (recordSet.next()) {
                res.add(recordSetToMap(recordSet));
            }
        }
        return res;
    }

    /**
     * 分页查询返回List<Map<String><String>>
     * 请使用   executePage方法进行替代
     */
    @Deprecated
    public static List<Map<String, String>> executeQueryToMapListPage(String primaryKey, String table, String criteria, List<String> fields, int start, int limit, Object... objs) {
        RecordSet recordSet = new RecordSet();
        String dbType = recordSet.getDBType().toUpperCase();
        // 判断数据库类型
        log.debugWriteLog("dbType ===> " + dbType);
        String sql = "";
        String fieldStr = Joiner.on(",").skipNulls().join(fields.toArray());
        // 判断当前数据库类型
        if (dbType.contains("MYSQL")) { // MySQL
            sql = String.format("select %s from %s %s limit %s, %s", fieldStr, table, criteria, start, limit);
        } else { // 默认 SQL SERVER
            sql = String.format(
                    "select top (%s) * from ( " +
                            "select row_number() over(order by %s.%s) as rownumber, %s from %s %s" +
                            ") temp_row where rownumber > %s"
                    , limit, table, primaryKey, fieldStr, table, criteria, start);
        }
        log.debugWriteLog("执行SQL => " + sql);
        log.debugWriteLog("SQL参数 => " + Arrays.toString(objs));
        List<Map<String, String>> res = Lists.newArrayList();
        if (recordSet.executeQuery(sql, objs)) {
            while (recordSet.next()) {
                res.add(recordSetToMap(recordSet));
            }
        }
        return res;
    }


    /**
     * 分页查询返回List<Map<String><String>>
     * 请使用   executePageMapList方法进行替代
     */
    @Deprecated
    public static List<Map<String, String>> executeQueryToMapListPage(String table, String criteria, List<String> fields, int start, int limit, Object... objs) {
        return executeQueryToMapListPage("id", table, criteria, fields, start, limit, objs);
    }

    public static Map<String, String> executeQueryToMap(String sql, Object... objs) {
        RecordSet recordSet = new RecordSet();
        log.debugWriteLog("执行SQL => " + sql);
        log.debugWriteLog("SQL参数 => " + Arrays.toString(objs));
        if (recordSet.executeQuery(sql, objs)) {
            if (recordSet.next()) {
                return recordSetToMap(recordSet);
            }
        }
        return null;
    }

    private static Map<String, String> recordSetToMap(RecordSet recordSet) {
        // 根据配置项目判断是否需要大小写敏感
        Map<String, String> map = Maps.newHashMap();
        if (caseinsensitive) {
            map = MapUtils.newCaseInsensitiveMap();
        }
        for (String s : recordSet.getColumnName()) {
            map.put(s, recordSet.getString(s));
        }
        return map;
    }

    public static void executeUpdate(String sql, List<Object> param) {
        RecordSet recordSet = new RecordSet();
        log.debugWriteLog("执行SQL => " + sql);
        log.debugWriteLog("SQL参数 => " + Arrays.toString(param.toArray()));
        recordSet.executeUpdate(sql, param.toArray(new Object[]{}));
    }

    public static void executeUpdate(String sql, Object... objs) {
        RecordSet recordSet = new RecordSet();
        log.debugWriteLog("执行SQL => " + sql);
        log.debugWriteLog("SQL参数 => " + Arrays.toString(objs));
        recordSet.executeUpdate(sql, objs);
    }

    public static void execute(String sql, List<Object> param) {
        RecordSet recordSet = new RecordSet();
        log.debugWriteLog("解析前SQL => " + sql);
        sql = toSingleSql(sql, param);
        log.debugWriteLog("执行SQL => " + sql);
        recordSet.execute(sql);
    }

    public static void batchExecute(String sql, List<List<Object>> param) {
        BatchRecordSet batchRecordSet = new BatchRecordSet();
        log.debugWriteLog("执行SQL => " + sql);
        batchRecordSet.executeBatchSql(sql, param);
    }

    private static String toSingleSql(String sql, List<Object> param) {
        int pos = 0;
        StringBuffer stringBuffer = new StringBuffer();
        for (int i = 0; i < sql.length(); i++) {
            if (sql.charAt(i) == '?') {
                stringBuffer.append(String.format("'%s'", param.get(pos++)));
            } else {
                stringBuffer.append(sql.charAt(i));
            }
        }
        return stringBuffer.toString();
    }

    public static void saveBean(String tableName, Object obj) throws IllegalAccessException {
        Map<String, Object> param = BUtils.beanToAttrMap(obj);
        StringBuilder sqlStr = new StringBuilder(String.format("insert into %s (", tableName));
        StringBuilder temp = new StringBuilder(" values (");
        List<Object> objects = Lists.newArrayList();
        int i = 0;
        for (Map.Entry<String, Object> entry : param.entrySet()) {
            if (i++ == 0) {
                sqlStr.append(entry.getKey());
                temp.append("?");
            } else {
                sqlStr.append(", " + entry.getKey());
                temp.append(", ?");
            }
            objects.add(entry.getValue());
        }
        String sql = sqlStr.toString() + " )" + temp.toString() + " )";
        executeUpdate(sql, objects);
    }

    public static void saveBean(String tableName, Object obj, Map<String, String> fields) throws Exception {
//        Map<String, Object> param = BUtils.beanToAttrMap(obj);
        StringBuffer sqlStr = new StringBuffer(String.format("insert into %s (", tableName));
        StringBuffer temp = new StringBuffer(" values (");
        int i = 0;
        List<String> keys = Lists.newArrayList();
        for (Map.Entry<String, String> entry : fields.entrySet()) {
            if (i++ == 0) {
                sqlStr.append(entry.getValue());
                temp.append("?");
            } else {
                sqlStr.append(", " + entry.getValue());
                temp.append(", ?");
            }
            keys.add(entry.getKey());
        }
        List<Object> objects = BUtils.beanToAttrListWithSq(obj, keys);
        String sql = sqlStr.toString() + " )" + temp.toString() + " )";
        executeUpdate(sql, objects);
    }

    public static String analyzeInsertSql(String tableName, Map<String, String> param) {
        StringBuffer sql = new StringBuffer(String.format("insert into %s (", tableName));
        StringBuffer temp = new StringBuffer(" ) values (");
        int i = 0;
        for (Map.Entry<String, String> entry : param.entrySet()) {
            if (i++ == 0) {
                sql.append(entry.getValue());
                temp.append("?");
            } else {
                sql.append(", " + entry.getValue());
                temp.append(", ?");
            }
        }
        return sql.toString() + temp.toString() + ")";
    }

    public static List<List<Object>> analyzeObjList(List<?> list, List<String> fields) throws Exception {
        List<List<Object>> params = Lists.newArrayList();
        for (Object obj : list) {
            params.add(BUtils.beanToAttrListWithSq(obj, fields));
        }
        return params;
    }

    public static List<List<Object>> analyzeObjList(List<?> list, Map<String, String> fields) throws Exception {
        return analyzeObjList(list, new ArrayList<>(fields.keySet()));
    }

    public static <T> T querySqlToSingleBean(String sql, Class<T> clazz, Map<String, String> fields, Object... objs) {
        RecordSet recordSet = new RecordSet();
        log.debugWriteLog("执行SQL => " + sql);
        recordSet.executeQuery(sql, objs);
        if (recordSet.next())
            return recordSetMapToBean(recordSet, clazz, fields);
        return null;
    }

    public static PageInfo<Map<String, String>> executePageMapList(String originSql, PageQuery pageQuery, Object... objs) {
        RecordSet recordSet = new RecordSet();
        log.debugWriteLog("执行Page OriginSQL => " + originSql);
        String realSql = "";
        if (StringUtils.equalsIgnoreCase(recordSet.getDBType(), "mysql")) {
            realSql += String.format("%s limit %s, %s", originSql, pageQuery.getStartNum(), pageQuery.getLimit());
        } else if (StringUtils.equalsIgnoreCase(recordSet.getDBType(), "oracle")) {
            realSql = String.format("SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( %s ) TMP WHERE ROWNUM <=%s) WHERE ROW_ID > %s", originSql, pageQuery.getEndNum(), pageQuery.getStartNum());
        } else if (StringUtils.equalsIgnoreCase(recordSet.getDBType(), "sqlserver") || StringUtils.equalsIgnoreCase(recordSet.getDBType(), "mssql")) {
            //TODO 这个要注意，使用了两次originSql, 如果可变参数不为空的话需要两边
            realSql = String.format("SELECT top %s * FROM (%s) TMP1 where TMP1.id not in ( select top %s id from (%s) TMP2 )", pageQuery.getLimit(), originSql, pageQuery.getStartNum(), originSql);
        } else {
            throw new RuntimeException("未处理的数据库类型 => " + recordSet.getDBType());
        }
        log.debugWriteLog("执行RealSQL => " + realSql);
        log.debugWriteLog("SQL参数 => " + Arrays.toString(objs));
        List<Map<String, String>> res = Lists.newArrayList();
        if (recordSet.executeQuery(realSql, objs)) {
            while (recordSet.next()) {
                res.add(recordSetToMap(recordSet));
            }
        }
        return createPageInfo(res, Long.parseLong(Objects.requireNonNull(DaoUtils.querySingleVal(String.format("SELECT COUNT(1) FROM (%s) TMP", originSql), objs))), pageQuery);
    }

    private static PageInfo<Map<String, String>> createPageInfo(List<Map<String, String>> list, Long count, PageQuery pageQuery) {
        return new PageInfo<Map<String, String>>().setList(list).setPages(count
                % pageQuery.getLimit() == 0
                ? count / pageQuery.getLimit()
                : (count / pageQuery.getLimit()) + 1)
                .setCurrent(pageQuery.getPage()).setCount(count).setLimit(pageQuery.getLimit());
    }

//    =============================================> 第三方数据源操作支持 <=============================================
    public static List<Map<String, String>> dsExecuteQuery(String ds, String sql) {
        RecordSetDataSource recordSetDataSource = new RecordSetDataSource(ds);
        log.debugWriteLog("执行SQL => " + sql);
        List<Map<String, String>> res = Lists.newArrayList();
        if (recordSetDataSource.execute(sql)) {
            while(recordSetDataSource.next()) {
                res.add(recordSetToMap(recordSetDataSource));
            }
        }

        return res;
    }

    public static String dsQuerySingleVal(String ds, String sql) {
        RecordSetDataSource recordSetDataSource = new RecordSetDataSource(ds);
        log.debugWriteLog("执行SQL => " + sql);
        recordSetDataSource.execute(sql);
        return recordSetDataSource.next() ? recordSetDataSource.getString(1) : null;
    }

    public static void dsExecuteUpdate(String ds, String sql) {
        RecordSetDataSource recordSetDataSource = new RecordSetDataSource(ds);
        log.debugWriteLog("执行SQL => " + sql);
        recordSetDataSource.execute(sql);
    }


    private static Map<String, String> recordSetToMap(RecordSetDataSource recordSetDataSource) {
        // 根据配置项目判断是否需要大小写敏感
        Map<String, String> map = Maps.newHashMap();
        if (caseinsensitive) {
            map = MapUtils.newCaseInsensitiveMap();
        }
        for (String s : recordSetDataSource.getColumnName()) {
            map.put(s, recordSetDataSource.getString(s));
        }
        return map;
    }


}
